﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ActivitiesControl.Domain.DAL;
using ActivitiesControl.Domain.Model;
using System.Data.SqlClient;
using System.Data;
using ActivitiesControl.Domain.Utils;
using ActivitiesControl.Domain.Model.Entities;

namespace ActivitiesControl.Repository.DAL.SqlServer
{
    internal class SqlServerProjectCustomerRepository
         : SqlServerBaseRepository<Project>, IProjectRepository, ICustomerRepository
    {
        public IEnumerable<Project> GetProjectsByCustomerId(int customerId)
        {
            try
            {
                Connection.Open();
                SqlCommand cmd = Connection.CreateCommand();
                cmd.CommandText = "select * from Projects where customerId = @customerId";
                cmd.Parameters.AddWithValue("customerId", customerId);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable result = new DataTable();
                adapter.Fill(result);

                foreach (DataRow row in result.Rows)
                    yield return Row2Object(row);
            }
            finally
            {
                Connection.Close();
            }
        }

        public IEnumerable<Project> GetAll()
        {
            try
            {
                Connection.Open();
                SqlCommand cmd = Connection.CreateCommand();
                cmd.CommandText = "select * from Projects";
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable result = new DataTable();
                adapter.Fill(result);

                foreach (DataRow row in result.Rows)
                    yield return Row2Object(row);
            }
            finally
            {
                Connection.Close();
            }

        }

        public void Add(Project entity)
        {
            try
            {
                Connection.Open();
                SqlCommand cmd = Connection.CreateCommand();

                cmd.CommandText = "insert into Projects values(@customerId, @name)";

                cmd.Parameters.AddWithValue("customerId", entity.CustomerId);
                cmd.Parameters.AddWithValue("name", entity.Name);

                cmd.ExecuteNonQuery();
            }
            finally
            {
                Connection.Close();
            }
        }

        public void Update(Project entity)
        {
            throw new NotImplementedException();
        }

        public void Remove(Project entity)
        {
			RemoveById(entity.Id);
        }

		public void RemoveById(int id)
		{
			try
			{
				Connection.Open();
				SqlCommand cmd = Connection.CreateCommand();
				cmd.CommandText = "delete from Projects where projectId =  @projectId";
				cmd.Parameters.AddWithValue("projectId", id);
				cmd.ExecuteNonQuery();
			}
			finally
			{
				Connection.Close();
			}
		}

        public Project GetById(int id)
        {
            try
            {
                Connection.Open();
                SqlCommand cmd = Connection.CreateCommand();
                cmd.CommandText = "select * from Projects where projectId = @projectId";
                cmd.Parameters.AddWithValue("projectId", id);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable result = new DataTable();
                adapter.Fill(result);
                if (result != null && result.Rows != null && result.Rows.Count > 0)
                    return Row2Object(result.Rows[0]);

                return null;
            }
            finally
            {
                Connection.Close();
            }
        }

        protected override Project Row2Object(DataRow row)
        {
            return new Project
            {
                Id = Convert.ToInt32(row["projectId"]),
                Name = row["name"].ToString(),
                CustomerId = Convert.ToInt32(row["customerId"])
            };
        }

		protected Customer Row2Customer(DataRow row)
		{
			return new Customer
			{
				Id = Convert.ToInt32(row["customerId"]),
				Name = row["name"].ToString()
			};
		}

		#region IRepository<Customer,int> Members

		public void Add(Customer entity)
		{
			throw new NotImplementedException();
		}

		public void Update(Customer entity)
		{
			throw new NotImplementedException();
		}

		public void Remove(Customer entity)
		{
			throw new NotImplementedException();
		}

		Customer IRepository<Customer, int>.GetById(int id)
		{
			try
			{
				Connection.Open();
				SqlCommand cmd = Connection.CreateCommand();
				cmd.CommandText = "select * from Customers where customerId = @customerId";
				cmd.Parameters.AddWithValue("customerId", id);
				SqlDataAdapter adapter = new SqlDataAdapter(cmd);
				DataTable result = new DataTable();
				adapter.Fill(result);
				if (result != null && result.Rows != null && result.Rows.Count > 0)
					return Row2Customer(result.Rows[0]);

				return null;
			}
			finally
			{
				Connection.Close();
			}
		}

		IEnumerable<Customer> ICustomerRepository.GetCustomersByResourceId(int resourceId)
		{
			try
			{
				Connection.Open();
				SqlCommand cmd = Connection.CreateCommand();
				cmd.CommandText = "select c.* from Customers c inner join CustomerResources r on c.customerId = r.customerId where r.resourceId = @resourceId";
				cmd.Parameters.AddWithValue("resourceId", resourceId);
				SqlDataAdapter adapter = new SqlDataAdapter(cmd);
				DataTable result = new DataTable();
				adapter.Fill(result);

				var list = new List<Customer>();

				foreach (DataRow row in result.Rows)
					list.Add(Row2Customer(row));

				return list;
			}
			finally
			{
				Connection.Close();
			}
		}

		IEnumerable<Customer> IRepository<Customer, int>.GetAll()
		{
			throw new NotImplementedException();
		}

		#endregion

		#region ICustomerRepository Members

		

		#endregion
	}
}
